import pathlib
import sqlite3
import numpy as np
# from numpy import NaN
import time
import easygui #pip install easygui
import decimal
import os
import sys
import time
from openpyxl import Workbook
from openpyxl.styles import fills,colors,NamedStyle,Font,Side,Border,PatternFill,Alignment,Protection
from secret import *

PassRate=0.6 #及格率
ExcellentRate=0.85 #优秀率
LowScoresRate=0.3 #低分率
MissedTest=None #缺考 0 :按0分计算,None：按缺考计算
fsd=[0,200,250,300,350,400,450,500,550,600,610,620,630,640,650,701] #统计分数段
#pip install pycryptodome bce-python-sdk
#从Python SDK导入BOS配置管理模块以及安全认证模块
'''
from baidubce.bce_client_configuration import BceClientConfiguration #pip install pycryptodome bce-python-sdk
from baidubce.auth.bce_credentials import BceCredentials
#导入BOS相关模块
from baidubce import exception
from baidubce.services import bos
from baidubce.services.bos import canned_acl
from baidubce.services.bos.bos_client import BosClient
from baidubce.services.bos import storage_class

#设置BosClient的Host，Access Key ID和Secret Access Key

bucket_name='zthubo'
config = BceClientConfiguration(credentials=BceCredentials(access_key_id, secret_access_key), endpoint = bos_host)
bos_client = BosClient(config)
'''
# 统计科目=["地理", "生物"] #temp
def choiceStatistics(): #选择统计科目
    StatisticsList={
        "七年级":{
            "subjects":["语文", "数学", "英语", "政治", "历史", "地理", "生物"],
            "fp":{'语文':100,'数学':100,'英语':100,'政治':40,'历史':40,'生物':40,'地理':30,'总分':450}
        }
        ,"七年级(无实验分)":{
            "subjects":["语文", "数学", "英语", "政治", "历史", "地理", "生物"],
            "fp":{'语文':100,'数学':100,'英语':100,'政治':40,'历史':40,'生物':36,'地理':30,'总分':446}
        }
        ,"八年级":{
            "subjects":["语文", "数学", "英语", "政治", "历史", "地理", "生物", "物理"],
            "fp":{'语文':100,'数学':100,'英语':100,'政治':40,'历史':40,'生物':40,'地理':30,'物理':50,'总分':500}
        }   
        ,"八年级(无实验分)":{
            "subjects":["语文", "数学", "英语", "政治", "历史", "地理", "生物", "物理"],
            "fp":{'语文':100,'数学':100,'英语':100,'政治':40,'历史':40,'生物':36,'地理':30,'物理':45,'总分':491}
        }      
        ,"八年级地理生物":{
            "subjects":[ "地理", "生物"],
            "fp":{'生物':40,'地理':30,'总分':70}
        }
        ,"九年级7科目":{
            "subjects":["语文", "数学", "英语", "政治", "历史", "物理", "化学"],
            "fp":{'语文':100,'数学':100,'英语':100,'物理':50,'化学':30,'政治':40,'历史':40,'总分':460}
        }  
        ,"九年级7科目(无实验分)":{
            "subjects":["语文", "数学", "英语", "政治", "历史", "物理", "化学"],
            "fp":{'语文':100,'数学':100,'英语':100,'物理':45,'化学':27,'政治':40,'历史':40,'总分':452}
        }   
        ,"九年级全科目":{
            "subjects":["语文", "数学", "英语", "政治", "历史", "物理", "化学", "地理", "生物"],
            "fp":{'语文':100,'数学':100,'英语':100,'物理':50,'化学':30,'政治':40,'历史':40,'生物':40,'地理':30,'总分':530}
        }
        ,"周考统计，总分700分":{
            "subjects":["语文", "数学", "英语", "政治", "历史", "物理", "化学", "体育","生地信","劳音美实"],
            "fp":{'语文':100,'数学':100,'英语':100,'物理':45,'化学':27,'政治':40,'历史':40,'体育':100,'劳音美实':72,'生地信':76,'总分':700}
        }
        ,"周考统计，无小科目":{
            "subjects":["语文", "数学", "英语", "政治", "历史", "物理", "化学"],
            "fp":{'语文':100,'数学':100,'英语':100,'物理':45,'化学':27,'政治':40,'历史':40,'体育':100,'实验':12,'其它小科目总分':140,'生物':36,'地理':30,'总分':460}
        }
    }
    ch=[]
    for key in StatisticsList:
        ch.append(key)
    ch=easygui.choicebox("选择统计年级及科目，成绩为卷面分！","选择年级科目",ch)
    global 统计科目
    统计科目=StatisticsList[ch]["subjects"]
    global fullPoint
    fullPoint=StatisticsList[ch]["fp"]
    return StatisticsList[ch]

# 统计科目=["语文", "数学", "英语", "政治", "历史", "地理", "生物",] #原始成绩 七年级
# fullPoint={'语文':100,'数学':100,'英语':100,'政治':40,'历史':40,'生物':40,'地理':30,'总分':450} #折合满分成绩

# 统计科目=["语文", "数学", "英语", "政治", "历史", "地理", "生物", "物理",] #原始成绩 八年级
# fullPoint={'语文':100,'数学':100,'英语':100,'政治':40,'历史':40,'生物':40,'地理':30,'物理':50,'总分':500}

# 统计科目=["语文", "数学", "英语", "政治", "历史", "物理", "化学", "地理", "生物",] #原始成绩 九年级 全科目
# fullPoint={'语文':100,'数学':100,'英语':100,'物理':50,'化学':30,'政治':40,'历史':40,'生物':40,'地理':30,'总分':530}

# 统计科目=["语文", "数学", "英语", "政治", "历史", "物理", "化学", ] #原始成绩 九年级 7科目
# fullPoint={'语文':100,'数学':100,'英语':100,'物理':50,'化学':30,'政治':40,'历史':40,'总分':460}

# 统计科目=["语文", "数学", "英语", "政治", "历史", "物理", "化学", "体育", "实验", "其它小科目总分"] #9 700
# 统计科目=["语文", "数学", "英语", "政治","历史", "地理", "生物","物理"] #8 491
# 统计科目=["语文", "数学", "英语", "政治", "历史",  "生物", "地理"] #7 446
# fullPoint={'语文':100,'数学':100,'英语':100,'物理':45,'化学':27,'政治':40,'历史':40,'体育':100,'实验':72,'其它小科目总分':80,'生物':36,'地理':30,'总分':700}
root=pathlib.Path(os.path.dirname(os.path.realpath(sys.argv[0])))

bd = Border(
    left=Side(border_style="thin",
                color='000000'),
    right=Side(border_style="thin",
                color='000000'),
    top=Side(border_style="thin",
                color='000000'),
    bottom=Side(border_style="thin",
                color='000000')
                )
alignment=Alignment(horizontal='center',#水平'center', 'centerContinuous', 'justify', 'fill', 'general', 'distributed', 'left', 'right'
            vertical='center',#垂直'distributed', 'bottom', 'top', 'center', 'justify'
            text_rotation=0,#旋转角度0~180
            wrap_text=False,#文字换行
            shrink_to_fit=True,#自适应宽度，改变文字大小,上一项false
            indent=0)
fille = PatternFill('solid',fgColor='e5ffe5') #设置填充颜色为 橙色

db=sqlite3.connect(str(root/"result.db"))
cur = db.cursor()


bj=[]
def getbj():
    r=cur.execute("select * from bj")
    for i in r.fetchall():  # 班级列表
        bj.append(i[0])
    bj.append("全校")
    return bj
def sqlbj1(sql,bj):
    if(bj=="全校"):
        return sql
    else:
        if(sql.find("where")==-1 and sql.find("WHERE")):
            return sql + "where  \"班级\"=\"{bj}\"".format(bj=bj)
        else:
            return sql + "and  \"班级\"=\"{bj}\"".format(bj=bj)

def getsortdata():  
    dtype=[('name',"U5"),('bj',"U5")]
    sql="" 
    for i in 统计科目:
        dtype.append((i,'float'))
        sql=f"{sql},{i}"
    sql=f"select 姓名,班级{sql},总分,0,0 from base"
    result=cur.execute(sql)
    result=result.fetchall()
    # dtype=[('name',"U5"),('bj',"U5"),('yw',float),('sx',float),('yy',float),('wl',float),('hx',float),('zz',float),('ls',float),('ty',float),('sy',float),('xkm',float),('zf',float),('bmc',int),('xmc',int)]
    dtype.append(('zf','float'))
    dtype.append(('bmc','int'))
    dtype.append(('xmc','int'))
    data=np.array(result,dtype=dtype)
    for i,v in enumerate(data):
        if(np.isnan(v['zf'])):
            data[i]['zf']=0

    data.sort(order="zf")
    data=np.flipud(data)
    xmc=1
    for i,v in enumerate(data):
        data[i]['xmc']=xmc
        xmc+=1


    data.sort(order=["bj","zf"])
    data=np.flipud(data)
    bj_t=""
    for i,v in enumerate(data):
        if(v["bj"]!=bj_t):
            bj_t=v["bj"]
            bmc=1

        data[i]['bmc']=bmc
        bmc+=1
    #data.sort(order=["bj","bmc"])
    data.sort(order=["xmc"])
    return data


def sqlbj(bj):
    #select 语文,数学,英语,物理,化学,政治,历史,体育,实验,小科目,总分 from base 
    sql="select "
    for i  in 统计科目 :
        sql= f"{sql} {i} , "
    sql=f"{sql} 总分 from base "
    if(bj=="全校"):
        return sql
    else:
        if(sql.find("where")==-1 and sql.find("WHERE")):
            return sql + "where  \"班级\"=\"{bj}\"".format(bj=bj)
        else:
            return sql + "and  \"班级\"=\"{bj}\"".format(bj=bj)


def calc(num,type):
    try:
        if type=="平均分":
            return np.nanmean(num,0)
        elif type=="最低分":
            return np.nanmin(num,0)
        elif type=="最高分":
            return np.nanmax(num,0)
        elif type=="标准差":
            return np.nanstd(num,0)   
    except:
        easygui.exceptionbox("数据模板不正确，请使用正确的模板导入数据")
        exit()

def counts(type,bj):
    global fullPoint
    # if(type=='总人数'):
    #     f={'语文':0,'数学':0,'英语':0,'物理':0,'化学':0,'政治':0,'历史':0,'体育':0,'实验':0,'小科目':0,'总分':0}
    # elif(type=='及格人数'):
    #     f={'语文':60,'数学':60,'英语':42,'物理':27,'化学':16.2,'政治':24,'历史':24,'体育':60,'实验':18,'小科目':42,'总分':391.2}
    # elif(type=='优秀人数'):
    #     f={'语文':80,'数学':80,'英语':56,'物理':36,'化学':21.6,'政治':32,'历史':32,'体育':80,'实验':24,'小科目':56,'总分':521.6}
    # elif(type=='低分人数'):
    #     f={'语文':30,'数学':30,'英语':21,'物理':13.5,'化学':8.1,'政治':12,'历史':12,'体育':30,'实验':9,'小科目':21,'总分':195.6}
    if(bj=="全校"):
        bj=""
    else:
        bj=" and  班级 = \'{bj}\'".format(bj=bj)
    list=[]
    for i in 统计科目+["总分"]:
        if(type=='总人数'):
            sql=f"select count(*) as c from base where {i} notnull {bj} and {i} >=0 "
        elif(type=='及格人数'):
            sql=f"select count(*) as c from base where {i} notnull {bj} and {i} >= {fullPoint[i]*PassRate}"
        elif(type=='优秀人数'):
            sql=f"select count(*) as c from base where {i} notnull {bj} and {i} >= {fullPoint[i]*ExcellentRate}"
        elif(type=='低分人数'):
            sql=f"select count(*) as c from base where {i} notnull {bj} and {i} < {fullPoint[i]*LowScoresRate}"
        elif(type=='中间人数'):
            sql=f"select count(*) as c from base where {i} notnull {bj} and {i} >= {fullPoint[i]*LowScoresRate} and {i} < {fullPoint[i]*PassRate}"
        r=cur.execute(sql)
        r=r.fetchone()
        list.append(r[0])
    return list

def countfsd(f):#总分分数段统计
    countlist={}
    for b in bj:
        countlist[b]=[]
        for v in range(0,len(f)-1):
            sql="select count(*) from base where 总分>{} and 总分<={} ".format(f[v],f[v+1])
            sql=sqlbj1(sql,b)
            r=cur.execute(sql)
            r=r.fetchone()
            countlist[b].append(r[0])
    return countlist


def genXlsx(data,f,file):
    wb=Workbook()
    ws1=wb.create_sheet("统计")
    ws2=wb.create_sheet("汇总")
    ws1.cell(1,1,"{}年鲁甸县新街中学测试成绩统计".format(time.strftime("%Y",time.localtime())))
    ws1.merge_cells(start_row=1, end_row=1, start_column=1, end_column=len(统计科目)+3)
    ws1.cell(1,1).font=Font(size=20) #字体
    ws1.cell(1,1).alignment=alignment
    ws1.row_dimensions[1].height = 32   #行高
    ws1.freeze_panes = 'B3' #冻结
    ws1.cell(2,1,"班级").border=bd
    ws1.merge_cells(start_row=2, end_row=2, start_column=1, end_column=2)
    ws1.cell(2,1).alignment=alignment
    for i in 统计科目:
        ws1.cell(2,统计科目.index(i)+3,i).border=bd
    ws1.cell(2,len(统计科目)+3,"总分").border=bd
    for i in range(3,len(统计科目)+4):
        ws1.cell(2,i).alignment=alignment
    rows=3
    cols=2
    for d in data:

        ws1.cell(rows,1,d).border=bd
        ws1.cell(rows,1).alignment=alignment
        ws1.merge_cells(start_row=rows, end_row=rows+len(bj)-1, start_column=1, end_column=1)
        n=0
        for i in bj:
            if (rows%2==1):
                for ii in range(2,len(统计科目)+4):
                    ws1.cell(rows,ii).fill=fille
            ws1.cell(rows,cols,i).border=bd
            cols=cols+1
            for x in data[d][n]:
                ws1.cell(rows,cols,x).border=bd
                ws1.cell(rows,cols).number_format = f.get(d,'0.000')
                cols=cols+1
            cols=2
            n=n+1
            rows=rows+1

    #分数段统计
    
    rows=rows+1
    ws1.cell(rows,1,'分数分布统计')
    fs=countfsd(fsd)
    ws1.merge_cells(start_row=rows, end_row=rows, start_column=1, end_column=2+len(fs))
    ws1.cell(rows,1).font=Font(size=20) #字体
    ws1.cell(rows,1).alignment=alignment
    ws1.row_dimensions[rows].height = 32   #行高
    rows=rows+1
    cols=1
    ws1.cell(rows,cols,'班级').border=bd
    for i in range(0,len(fsd)-1):
        cols=cols+1
        ws1.cell(rows,cols,"{:.0f}-{:.0f}".format(fsd[i],fsd[i+1]-1)).border=bd
    
    for i in bj:

        cols=1
        rows=rows+1
        ws1.cell(rows,cols,i).border=bd
        if (rows%2==1):
            for ii in range(1,1+len(fsd)):
                ws1.cell(rows,ii).fill=fille        
        for n in fs[i]:
            cols=cols+1
            ws1.cell(rows,cols,n).border=bd
    rows=rows+2
    cols=2
    qkt="不参与统计" if MissedTest is None else "按0分统计"
    ws1.cell(rows,cols,"生成时间：{}，缺考考生{}".format(time.strftime("%Y年%m月%d日",time.localtime()),qkt))
    #明细    
    cols=1
    rows=1
    title=['姓名','班级'] #,'语文','数学','英语','物理','化学','政治','历史','体育','劳音美实','生地信','总分','班级排名','年级排名']
    for kmt in 统计科目:
        title.append(kmt)
    title=title+['总分','班级排名','年级排名']
    ws2.freeze_panes = 'B2' #冻结
    for i in title:
        ws2.cell(rows,cols,i).border=bd
        ws2.cell(rows,cols).alignment=alignment
        cols=cols+1
    data=getsortdata()
    for i,v in enumerate(data):
        if(v["zf"]==0):
            v["zf"]=np.nan
        cols=1
        rows=rows+1
        if (rows%2==1):
            for ii in range(1,len(title)+1):
                ws2.cell(rows,ii).fill=fille 
        for n in v:
            if str(n)=="nan" :
                ws2.cell(rows,cols,"缺考").border=bd
            else:
                ws2.cell(rows,cols,n).border=bd
            cols=cols+1


    wb.remove(wb["Sheet"])  
    i=0 
    while(True):
        try:
            wb.save(pathlib.Path(file).parent/(pathlib.Path(file).stem+"统计.xlsx"))
        except:
            if(i>=5):
                easygui.msgbox("文件保存失败!")
                exit()
            t=easygui.exceptionbox("文件保存失败，请关闭Excel")
            
            print(t)
        else:
            break
        i=i+1
    fs=countfsd(fsd)
    print(fs)

def convert(d,t):
    if(d==None or d=="None" or d=="" or d=="缺考"):
        return MissedTest
    if(t=="物理"):
        return decimal.Decimal(str(d))*decimal.Decimal("0.5")
    elif(t=="化学" or t=="地理"):
        return decimal.Decimal(str(d))*decimal.Decimal("0.3")
    elif(t=="政治" or t=="历史" or t=="生物"  ):
        return decimal.Decimal(str(d))*decimal.Decimal("0.4")
    else:
        return (decimal.Decimal(str(d)) if d != None else None)
def gethtml(data,fm):
    '''
    html=""
    with open(root/'index.xml','r+',encoding='utf8') as f :
        text=f.read()
    fs=countfsd(fsd)
    for i in range(0,len(fsd)-1):
        html+="<td>{:.0f}-{:.0f}</td>".format(fsd[i],fsd[i+1]-1)
    html+="</tr>"
    for i in bj:
        html+="<tr><td>{}</td>".format(i)
        for n in fs[i]:
            html+="<td>{}</td>".format(n)
    html+="</tr>"

    text=text.replace(r"<!--content2-->",html)
    html=""
    for d in data:
        html+="<tr><td style=\"vertical-align: middle;\" rowspan=\"{count_}\"><div style=\"width: 20px;line-height: 18px; margin:0 auto;\">{vt}</div></td>".format(count_=len(bj),vt=d)
        n=0
        for i in bj:
            html+="<td>{x}</td>".format(x=i)
            for x in data[d][n]:
                html+=("<td>{x}</td>".format(x=fm.get(d,"{x:.3f}"))).format(x=x)
            html+="</tr><tr>"
            n+=1
        html.rstrip("<tr>")
    text=text.replace(r"<!--content1-->",html)
    text=text.replace(r"<!--time-->",time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()))

    try:
        bos_client.put_object_from_string(bucket_name, "index.html", text,content_type = 'text/html')
    except:
        print("https://zthubo.bj.bcebos.com/index.html 上传失败！")
    with open(root/'index.html','w+',encoding='utf8') as f :
        f.write(text)
'''
def getDetailed():
        '''
    data=getsortdata()
    html=""
    for i,v in enumerate(data):
        if(v["zf"]==0):
            zf="缺考"
        else:
            zf="{0:.1f}".format(v["zf"])  #287.3 bug
        html+="<tr><td>{0[name]: <6s}</td><td>{0[yw]}</td><td>{0[sx]}</td><td>{0[yy]}</td><td>{0[wl]}</td><td>{0[hx]}</td><td>{0[xkm]}</td><td>{0[bj]}</td><td>{1}</td><td>{0[xmc]}</td><td>{0[bmc]}</td></tr>".format(v,zf)

    with open(root/'Detailed.xml','r+',encoding='utf8') as f :
        text=f.read()
    text=text.replace(r"<!--content1-->",html)
    text=text.replace(r"nan","缺考")
    text=text.replace(r"<!--time-->",time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()))

    try:
        bos_client.put_object_from_string(bucket_name, "Detailed.html", text,content_type = 'text/html')
    except:
        print("https://zthubo.bj.bcebos.com/ 上传失败！")
    with open(root/'Detailed.html','w+',encoding='utf8') as f :
        f.write(text)
        '''
